We have an MS/Access database that works fine in Office 2007 but in Office 2010, the user cannot update it. She receives the msg "The recordset is not updateable".

I tried it on Windows 7 and Windows/XP with Office 2010 but get the same result. I have reviewed possible causes of this msg. The tables have primary keys and have not changed. I ensured that the application is trusted and is in a trusted location. It does run macros and I suspect it has something to do with that. I also tried changing the sandbox mode to permit it to run anything...same result. I want to stress that the db is exactly the same on both Access 2007 and Access 2010. Has anyone else run into a similar problem?

 

Hi,

Here are 2 very common reasons for "not updatable" recordsets.

1. Missing primary keys: Make sure all of your tables have a primary key. Use an autonum if you must, but you are better of using a unique field or concatenated unique fields.

2. Grouping (Totals) queries: If you are using a grouping query as a subquery, it will not be updatable. Make a temp table out of the grouping query and use the temp table in place of the subquery. Also try to refer to these articles: http://support.microsoft.com/kb/328828 http://www.fmsinc.com/microsoftaccess/query/non-updateable/index.html Hope it helps.

• Marked as answer by Jennifer Zhan Friday, September 17, 2010 1:29 AM
• Unmarked as answer by David Wolters Thursday, September 23, 2010 6:25 PM

Thanks but I have checked those things. The only thing that has changed is the version of Access. It works fine in Access/2007 but does not work in Access/2010.

 

Doug,

I am having the same issue after converting to Office 2010.  Did you ever find a solution?  Thanks.

I am having the same issue.    I confirm that it used to work in 2007 and that I am not forgetting to have primary keys, etc.. etc..    

I am having some luck making it work by going into the query in design mode.  Then I click on Propery Sheet.  Then I make the RecordSet Type property be "Dynaset (Inconsistent Updates)".    (not just Dynaset).  

 

 

• Proposed as answer by Christoffer Vig Friday, May 11, 2012 8:42 PM

Same here and I'm not even using a query.  Works fine in previous versions of ACCESS (using the same machine and login) but not in ACCESS 2010 due to "recordset not updateable."  Any help would be greatly appreciated.

 

Form code:

    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
   
    Set cn = CurrentProject.AccessConnection
    Set rs = New ADODB.Recordset
   
    Set rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM MyTable WHERE ID = '" & ID.Value & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
   
    rs.Open
   
    Set Me.Recordset = rs

Same problem here but mine is going from Access 2003 to Access 2007.  I had a query in 2003 in which I could update values in my query but in 2007 I get the message 'This record set is not updateable'.  I tried "Dynaset (Inconsistent Updates)" that was suggested ealier but had not luck with this in 2007.  I'll keep working on it but was wondering if anyone has come up with a fix?   

Form code:

    Dim rs As ADODB.Recordset
    Dim cn As ADODB.Connection
   
    Set cn = CurrentProject.AccessConnection
    Set rs = New ADODB.Recordset
   
    Set rs.ActiveConnection = cn
    rs.Source = "SELECT * FROM MyTable WHERE ID = '" & ID.Value & "'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
   
    rs.Open
   
    Set Me.Recordset = rs

Something is wrong with this code. I've never assigned a recordset to Me.Recordset.

You can accomplish the same with just

    Me.RecordSource = "SELECT * FROM MyTable WHERE ID = " & ID.Value

or

    Me.RecordSource = "SELECT * FROM MyTable WHERE ID = '" & ID.Value & "'" if you really have string IDs ...

Or if you like some more work: use Bookmark to search for the right record and align Me.Bookmark (See Access Help for Bookmark).

I CAN'T BELIEVE IT!  I've also had this problem since I changed to Office 2010, and it's frustrated me as much as anyone. 

I just found my solution, and I can't believe how simple it was.  Any location where you are updating data must be included in your TRUSTED LOCATIONS.

To add a trusted location from within MS Access 2010:

1) Click on File

2) Click on then Options

3) In the resulting window, Select 'Trust Center" on the left

4) Click the "Trust Center Settings" button.

5) Select "Trusted Locations" on the left side.

6) Use the "Add new location" button to navigate to the folder where your file exists.  If you select a parent folder for many files, MAKE SURE you also check the "Subfolders of this location are also trusted".

One important final clue, if you are working with files on the network, make sure you select "Allow Trusted Locations on my network" in the trust center.

I'm having the same problem but only in one of my databases.  What finally worked was setting the properties on the FORMS and SUBFORMS to Dynaset (Inconsistent).  Changing that setting in the QUERIES did no good.  I had also added the network and my C: drive (and all subfolders of both) as trusted locations and turned off macro security - so it may even have been a combination of those things.

For me, the underlying tables and query could be edited directly, but the form was giving the 'not updateable' status message.  By the way, this came out of nowhere in a database that has been in service for years.  Our migration from Office 2007 happened over a year ago.

Anyways, going to the Design View of the offending form and changing the Recordset Type from 'Dynaset' to 'Dynaset (Inconsistent Updates)' did the trick.  I'm researching it now to determine what other effects might result.

Thanks a bunch guys, this helped me I had a form I could not for the life of me uncheck the stupid check box or update any fields because of this error so I changed it from dynaset to Dynaset (Inconsistent Updates), worked like a freaking charm! Thanks again!!!!

I've sen recent activity here, so I decided to point out there is a really weird BUG resulting from sorting the table in Dataset view on a field that isn't a primary key.

http://social.technet.microsoft.com/Forums/en-US/officesetupdeployprevious/thread/e13140dc-6005-49e8-9ad1-7df434960db0 (scroll all the way to the bottom).

The quick solution: Add a field, delete the field.   This resets something that then allows the table to be edited.

In case this helps anyone, I had a different cause for this issue.

When we converted from one SQL Server to another, the people here deleted all of the linked tables and set them up again for each Access MDB.

For one table (which was actually pointing to a SQL View, and which was the linked table it was querying for the problem-causing form), the people didn't check off the column to specify the linked table's "index" (IIRC) columns... even though there was even a little comment in the dialog that it was necessary to check off a field(s) if you wanted to update.  Luckily, the view was selecting an ID, so I tried that and it worked.  (If that hadn't worked, I was going to specify the view column that formed the key of the SQL table that was the table being updated by the form.)

I had seen comments on forums about how "all of the update form's tables need to have an index", and I'd checked the SQL tables that the view was referencing, but it hadn't occurred to me that "table" meant the Access "linked table".  The only place I saw to specify the index on the linked table was when re-adding the linked table.

   I found that re-linking the table made a difference.   The problem started for no apparent reason

I've sen recent activity here, so I decided to point out there is a really weird BUG resulting from sorting the table in Dataset view on a field that isn't a primary key.

<deleted>

The quick solution: Add a field, delete the field.   This resets something that then allows the table to be edited.

 Brilliant, thank-you!

Thank you, I have been trying to figure this out for months!  My screech of joy scared the crap out of my dogs!